package cn.edu.sdjzu.xg.kcsj.dao;


import cn.edu.sdjzu.xg.kcsj.domain.*;
import cn.edu.sdjzu.xg.kcsj.service.ClassroomService;
import cn.edu.sdjzu.xg.kcsj.service.CourseService;
import cn.edu.sdjzu.xg.kcsj.service.StudentClassService;
import cn.edu.sdjzu.xg.kcsj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class CourseArrangementDao {
    private static CourseArrangementDao courseArrangementDao = new CourseArrangementDao();
    private CourseArrangementDao(){}
    public static CourseArrangementDao getInstance(){
        return courseArrangementDao;
    }

    // SELECT * FROM teacher WHERE name LIKE "%高%" AND no = "yyyy"
    public Collection<CourseArrangement> findAll (Connection connection, List<Condition> conditionList_json_str, Pagination pagination) throws SQLException {
        Collection<CourseArrangement>desiredcourseArrangements = new HashSet<CourseArrangement>();
        StringBuilder sql_select  = new StringBuilder ("SELECT * FROM coursearrangement");
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause (conditionList_json_str);
            sql_select.append (whereClause);
        }
        int totalNum = CourseArrangementDao.getInstance ().count(connection);
        if (pagination != null){
            sql_select.append (pagination.toLimitClause (totalNum)).append (" "); // Limit x1,x2
        }

        PreparedStatement preparedStatement = connection.prepareStatement (sql_select.toString ());
        ResultSet resultSet = preparedStatement.executeQuery ();
        while (resultSet.next ()){
            int id = resultSet.getInt ("id");
            String no = resultSet.getString ("no");
            String year = resultSet.getString ("year");
            String term = resultSet.getString ("term");
            String week = resultSet.getString ("week");
            String weekTime = resultSet.getString ("weekTime");
            String section = resultSet.getString ("section");
            Integer course_id = resultSet.getInt("course_id");
            Course course = CourseService.getInstance().find(course_id);
            Integer studentClass_id = resultSet.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            Integer classroom_id = resultSet.getInt("classroom_id");
            Classroom classroom = ClassroomService.getInstance().find(classroom_id);
            Integer teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            CourseArrangement courseArrangement = new CourseArrangement(id,no,year,term,week,weekTime,section,course,studentClass,classroom,teacher);
            courseArrangement.setId (id);
            desiredcourseArrangements.add (courseArrangement);
        }
        JdbcHelper.close (resultSet,preparedStatement);
        return desiredcourseArrangements;
    }


    public CourseArrangement find (int id,Connection connection) throws SQLException {
        CourseArrangement courseArrangement = null;
        PreparedStatement pstmt_select_id = connection.prepareStatement("SELECT * FROM coursearrangement" + " WHERE id = ?");
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        if(resultSet_select_id.next()){
            String no = resultSet_select_id.getString ("no");
            String year = resultSet_select_id.getString ("year");
            String term = resultSet_select_id.getString ("term");
            String week = resultSet_select_id.getString ("week");
            String weekTime = resultSet_select_id.getString ("weekTime");
            String section = resultSet_select_id.getString ("section");
            Integer course_id = resultSet_select_id.getInt("course_id");
            Course course = CourseService.getInstance().find(course_id);
            Integer studentClass_id = resultSet_select_id.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            Integer classroom_id = resultSet_select_id.getInt("classroom_id");
            Classroom classroom = ClassroomService.getInstance().find(classroom_id);
            Integer teacher_id = resultSet_select_id.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            courseArrangement  = new CourseArrangement(id,no,year,term,week,weekTime,section,course,studentClass,classroom,teacher);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id);
        return courseArrangement;
    }

    private int count (Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement ("SELECT * FROM Course");
        ResultSet resultSet = preparedStatement.executeQuery ();
        int r = 0;
        while (resultSet.next ()){
            r++;
        }
        return r;
    }

    public boolean add (CourseArrangement courseArrangement,Connection connection) throws SQLException {
        String add_sql = "INSERT INTO CourseArrangement(no, year,term,week,weekTime,section,course_id,studentclass_id,classroom_id,teacher_id ) VALUES (?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement (add_sql);
        ps.setString (1,courseArrangement.getNo ());
        ps.setString (2,courseArrangement.getYear());
        ps.setString (3,courseArrangement.getTerm());
        ps.setString (4,courseArrangement.getWeek());
        ps.setString (5,courseArrangement.getWeekTime());
        ps.setString (6,courseArrangement.getSection());
        ps.setInt (7,courseArrangement.getCourse().getId());
        ps.setInt (8,courseArrangement.getStudentClass().getId());
        ps.setInt (9,courseArrangement.getClassroom().getId());
        ps.setInt(10,courseArrangement.getTeacher().getId());
        int add_affectedRowNums = ps.executeUpdate ();
        JdbcHelper.close (ps,null);
        return add_affectedRowNums > 0;
    }

    public boolean delete (int id,Connection connection) throws SQLException {
        String delect_sql = "DELETE FROM CourseArrangement WHERE id = ?";
        PreparedStatement p = connection.prepareStatement (delect_sql);
        p.setInt (1,id);
        int delete_affectedRowNums = p.executeUpdate ();
        JdbcHelper.close (p,connection);
        return delete_affectedRowNums > 0;
    }

    public boolean update (CourseArrangement courseArrangement,Connection connection) throws SQLException {
        String update_sql = "UPDATE CourseArrangement SET no=?, year =?,term=?,week=?,weekTime=?,section =?,course_id =?,studentclass_id=?,classroom_id=?,teacher_id=? WHERE id=?";
        PreparedStatement ps = connection.prepareStatement (update_sql);
        ps.setString (1,courseArrangement.getNo ());
        ps.setString (2,courseArrangement.getYear());
        ps.setString (3,courseArrangement.getTerm());
        ps.setString (4,courseArrangement.getWeek());
        ps.setString (5,courseArrangement.getWeekTime());
        ps.setString (6,courseArrangement.getSection());
        ps.setInt (7,courseArrangement.getCourse().getId());
        ps.setInt (8,courseArrangement.getStudentClass().getId());
        ps.setInt (9,courseArrangement.getClassroom().getId());
        ps.setInt(10,courseArrangement.getTeacher().getId());
        int update_affectedRowNums= ps.executeUpdate ();
        return update_affectedRowNums > 0;
    }
}
